"""
Case Type   : A兼容性-type功能增强
Case Name   : 在type中定义map成员函数，order by子句通过value运算符别名进行排序
Create At   : 2025/5/22
Owner       : wan005
Description :
    1.创建type
    2.创建type body
    3.创建表，插入数据
    4.通过value运算符别名进行排序
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
History     :
"""
import os
import unittest
from yat.test import Node
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class Security(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'---{os.path.basename(__file__)} start---')
        self.userNode = Node(node='PrimaryDbAmode')
        self.sh_primy = CommonSH()
        self.common = Common()
        self.db_name = self.userNode.db_name
        self.type = 't_048'
        self.table = 'tb_048'

    def test_security(self):
        text = '----step1:创建type;expect:成功----'
        self.logger.info(text)
        sql_cmd1 = f'create or replace type {self.type} as object(atri_empno ' \
                   f'number(4),atri_ename varchar2(10),atri_sal number(7,2),' \
                   f'atri_comm number(7,2),map member function compare ' \
                   f'return number) not final;'
        res1 = self.sh_primy.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.logger.info(res1)
        self.assertEqual('CREATE TYPE', res1, '执行失败' + text)

        text = '----step2:创建type body;expect:成功----'
        self.logger.info(text)
        sql_cmd2 = f'''
            create or replace type body {self.type} as
            map member function compare return number as
            begin
            return self.atri_sal - self.atri_comm;
            end;
            end;
            '''
        res2 = self.sh_primy.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.logger.info(res2)
        self.assertEqual('CREATE TYPE', res2, '执行失败' + text)

        text = '----step3:创建表，插入数据；expect:成功----'
        self.logger.info(text)
        sql_cmd3 = f'create table {self.table} of {self.type};' \
                   f'insert into {self.table}(atri_empno,atri_ename,atri_sal,' \
                   f'atri_comm) values (7369,\'smith\',800,0);' \
                   f'insert into {self.table}(atri_empno,atri_ename,atri_sal,' \
                   f'atri_comm) values (7902,\'ford\',3000,0);' \
                   f'insert into {self.table}(atri_empno,atri_ename,atri_sal,' \
                   f'atri_comm) values (7499,\'allen\',1600,300);' \
                   f'insert into {self.table}(atri_empno,atri_ename,atri_sal,' \
                   f'atri_comm) values (7521,\'ward\',400,500);' \
                   f'insert into {self.table}(atri_empno,atri_ename,atri_sal,' \
                   f'atri_comm) values (7839,\'king\',5000,0);'
        res3 = self.sh_primy.execut_db_sql(sql_cmd3, dbname=self.db_name)
        self.logger.info(res3)
        self.assertEqual(5, res3.count('INSERT'), '执行失败' + text)

        text = '----step4:通过value运算符别名进行排序;expect:成功----'
        self.logger.info(text)
        sql_cmd4 = f'select value(e) ve,e.atri_empno,e.atri_ename,e.atri_sal,' \
               f'e.atri_comm,e.atri_sal+e.atri_comm from {self.table} e order by ve;'
        res4 = self.sh_primy.execut_db_sql(sql_cmd4, dbname=self.db_name)
        self.logger.info(res4)
        row4, _ = self.common.parse_sql_query_res(res4)
        content = [['(7521,ward,400.00,500.00)', '7521', 'ward', '400.00',
        '500.00', '900.00'], ['(7369,smith,800.00,0.00)', '7369', 'smith',
          '800.00', '0.00', '800.00'], ['(7499,allen,1600.00,300.00)', '7499',
        'allen', '1600.00', '300.00', '1900.00'], ['(7902,ford,3000.00,0.00)',
        '7902', 'ford', '3000.00', '0.00', '3000.00'],
       ['(7839,king,5000.00,0.00)', '7839', 'king', '5000.00', '0.00',
        '5000.00']]
        self.assertEqual(content, row4, '执行失败' + text)

    def tearDown(self):
        text = '----step1:清理环境;expect:成功----'
        cle_cmd1 = f'drop table {self.table};'
        cle_res1 = self.sh_primy.execut_db_sql(cle_cmd1, dbname=self.db_name)
        self.logger.info(cle_res1)
        self.assertEqual('DROP TABLE', cle_res1, text + '执行失败')

        cle_cmd2 = f'drop type {self.type};'
        cle_res2 = self.sh_primy.execut_db_sql(cle_cmd2, dbname=self.db_name)
        self.logger.info(cle_res2)
        self.assertEqual('DROP TYPE', cle_res2, text + '执行失败')
        self.logger.info(f'---{os.path.basename(__file__)} end---')
